/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       TableService.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/17      feiwe                         add
 */


package com.fengwei.service;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.system.SystemUtil;
import com.fengwei.bean.Page;
import com.fengwei.executor.AbstractDbExecutor;
import com.fengwei.form.MdmsDatabase;
import com.fengwei.form.MdmsUser;
import com.fengwei.requestdata.table.TableDdlData;
import com.fengwei.requestdata.table.TableExportDataData;
import com.fengwei.requestdata.table.TablePagingListData;
import com.fengwei.util.GsonUtil;
import com.fengwei.util.PltResult;
import com.fengwei.util.SnowflakeIdWorker;
import com.fengwei.util.SpringContextUtil;
import com.fengwei.util.VerifyException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class TableService {
    private final static Logger logger = LoggerFactory.getLogger(TableService.class);

    @Autowired
    private SnowflakeIdWorker snowflakeIdWorker;

    public Map<String, Object> pagingList(TablePagingListData tablePagingListData, HttpServletRequest request) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        MdmsDatabase mdmsDatabase = (MdmsDatabase) session.getAttribute(tablePagingListData.getDatabaseRandomStr());
        if (null == mdmsDatabase) {
            throw new VerifyException(PltResult.RESULT_CF303);
        }

        String sql = "select * from " + tablePagingListData.getTable() + "";
        if (!StrUtil.hasEmpty(tablePagingListData.getCondition())) {
            sql += " where " + tablePagingListData.getCondition();
        }

        if (!StrUtil.hasEmpty(tablePagingListData.getSortStr())) {
            sql += " order by " + tablePagingListData.getSortStr();
        }

        logger.debug("sql:" + sql);

        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                mdmsDatabase.getDatabaseDbname());
        Connection connection = null;
        try {
            connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());
            List resultList = new ArrayList();
            StringBuffer sbRunLog = new StringBuffer();
            /*分页查询*/
            Page page = new Page();
            page.setPageNo(tablePagingListData.getPage());
            page.setPageSize(tablePagingListData.getLimit());
            Page<Map<String, Object>> pageResult = abstractDbExecutor.executeSelectSql(page, connection, sql);

            /*获取主键*/
            Map keyMap = abstractDbExecutor.getPrimaryKey(tablePagingListData.getTable(), connection);
            pageResult.setPrimaryKey(keyMap);
            logger.debug("pageResult:" + GsonUtil.toJson(pageResult, false));

            resultList.add(pageResult);

            returnData.put("resultList", resultList);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF304);
        } finally {
            abstractDbExecutor.closeConnection(connection);
        }

        return returnData;

    }

    public Map<String, Object> ddl(TableDdlData tableDdlData, HttpServletRequest request, MdmsUser mdmsUser) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        MdmsDatabase mdmsDatabase = (MdmsDatabase) session.getAttribute(tableDdlData.getDatabaseRandomStr());
        if (null == mdmsDatabase) {
            throw new VerifyException(PltResult.RESULT_CF303);
        }

        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                mdmsDatabase.getDatabaseDbname());
        Connection connection = null;
        try {
            connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());

            String ddl = abstractDbExecutor.ddl(connection, tableDdlData.getTable(), mdmsDatabase.getDatabaseUser());
            returnData.put("ddl", ddl);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF304);
        } finally {
            abstractDbExecutor.closeConnection(connection);
        }

        return returnData;
    }

    public Map<String, Object> exportData(TableExportDataData tableExportDataData, HttpServletRequest request) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        MdmsDatabase mdmsDatabase = (MdmsDatabase) session.getAttribute(tableExportDataData.getDatabaseRandomStr());
        if (null == mdmsDatabase) {
            throw new VerifyException(PltResult.RESULT_CF303);
        }

        String sql = "select * from " + tableExportDataData.getTable() + "";
        if (!StrUtil.hasEmpty(tableExportDataData.getCondition())) {
            sql += " where " + tableExportDataData.getCondition();
        }

        logger.debug("sql:" + sql);

        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                mdmsDatabase.getDatabaseDbname());
        Connection connection = null;

        String excelName = tableExportDataData.getTable() + snowflakeIdWorker.nextId() + ".xlsx";
        cn.hutool.system.UserInfo userInfo = SystemUtil.getUserInfo();
        FileUtil.mkdir(userInfo.getHomeDir() + File.separator + "exportData");
        ExcelWriter writer = ExcelUtil.getWriter(userInfo.getHomeDir() + File.separator + "exportData" + File.separator + excelName);
        try {
            connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());
            /*分页查询*/
            Page page = new Page();
            page.setPageNo(1);
            page.setPageSize(1000);
            Page<Map<String, Object>> pageResult = abstractDbExecutor.executeSelectSql(page, connection, sql);

            /*将查询到的记录写到excel当中*/
            List<Map<String, Object>> result = pageResult.getResult();
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(result, true);

            returnData.put("excelName", excelName);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF304);
        } finally {
            writer.close();
            abstractDbExecutor.closeConnection(connection);
        }

        return returnData;

    }
}
